﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class bas_user : BasePermissionPage
{
    SqlDataSource SDS_Company = new SqlDataSource();
    DataView dv = new DataView();
    DataView dv_ddl_1 = new DataView();
    DataView dv_ddl_2 = new DataView();
    
    protected void Page_Load(object sender, EventArgs e)
    {
        //== 1.連結資料庫的連接字串 ConnectionString  ，事先已經寫在 Web.Config檔案裡面了==
        SDS_Company.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SDS_Company.ProviderName = "Oracle.DataAccess.Client";
        SDS_Company.CancelSelectOnNullParameter = false;
        dv_ddl_1 = DDL_DEPT_DBINIT();
        dv_ddl_2 = DDL_ROLE_DBINIT();
        if (!IsPostBack)
        {
            //DropDownList資料
            if (dv_ddl_1 != null)
            {
                ddlDEPT_ID.DataSource = dv_ddl_1;
                ddlDEPT_ID.DataBind();
            }
            if (dv_ddl_2 != null)
            {
                ddlROLE_ID.DataSource = dv_ddl_2;
                ddlROLE_ID.DataBind();
            }
            //DBINIT();
        }
        
    }
    protected void DBINIT()
    {
        try
        {
            string v_USER_ID, v_SQL, v_USER_NAME, v_DEPT_ID, v_ROLE_ID;
            DataSourceSelectArguments args = new DataSourceSelectArguments();


            v_USER_ID = txtUSER_ID.Text.Trim();
            
            
            v_USER_NAME = txtUSER_NAME.Text.Trim();
            v_DEPT_ID = ddlDEPT_ID.SelectedValue;
            
            v_ROLE_ID = ddlROLE_ID.SelectedValue;
            
            //== 2.撰寫SQL指令 ==
            v_SQL = "SELECT A.USER_ID,A.USER_NAME,A.USER_PASSWORD,A.ROLE_ID,A.DEPT_ID,A.PARENT_USER_ID,A.E_MAIL1,A.E_MAIL2,A.DEPT_ID || '-' || B.DEPT_NAME as DEPT_NAME,A.ROLE_ID || '-' || C.ROLE_NAME as ROLE_NAME ";
            v_SQL = v_SQL + " FROM MIS_USER_AP A left join MIS_DEPT_AP B on A.DEPT_ID = B.DEPT_ID  left join MIS_ROLE_AP C on A.ROLE_ID = C.ROLE_ID  ";
            v_SQL = v_SQL + " Where (:USER_ID is null  or A.USER_ID = :USER_ID) and (A.USER_NAME like :USER_NAME) and (:DEPT_ID is null or A.DEPT_ID = :DEPT_ID)  and (:ROLE_ID is null or A.ROLE_ID = :ROLE_ID) ORDER BY A.DEPT_ID, A.USER_ID ";
            SDS_Company.SelectParameters.Clear();
            SDS_Company.SelectCommand = v_SQL;
            SDS_Company.SelectParameters.Add("1", TypeCode.String, v_USER_ID);
            SDS_Company.SelectParameters.Add("2", TypeCode.String, v_USER_ID);
            SDS_Company.SelectParameters.Add("3", TypeCode.String, "%" + v_USER_NAME + "%");
            SDS_Company.SelectParameters.Add("4", TypeCode.String, v_DEPT_ID);
            SDS_Company.SelectParameters.Add("5", TypeCode.String, v_DEPT_ID);
            SDS_Company.SelectParameters.Add("6", TypeCode.String, v_ROLE_ID);
            SDS_Company.SelectParameters.Add("7", TypeCode.String, v_ROLE_ID);

            //== 3.執行SQL指令 .select() ==
            SDS_Company.DataSourceMode = SqlDataSourceMode.DataSet;

            dv = (DataView)SDS_Company.Select(args);
            //dv.AddNew();
            DV1.DataSource = dv;


            DV1.DataBind();

            ;
        }
        catch (Exception ex)
        {
            //Response.Write(e.Message);
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DBINIT", ex.Message, "", "c#", "0", ex.StackTrace);

        }
    }
    protected DataView DDL_DEPT_DBINIT()
    {
        try
        {
            string v_SQL;
            DataView dv_ddl = new DataView();
            DataSourceSelectArguments args = new DataSourceSelectArguments();
            //== 2.撰寫SQL指令 ==
            v_SQL = "SELECT DEPT_ID,DEPT_ID || '-' || DEPT_NAME as DEPT_NAME FROM MIS_DEPT_AP order by DEPT_ID ";

            SDS_Company.SelectParameters.Clear();
            SDS_Company.SelectCommand = v_SQL;
            
            //== 3.執行SQL指令 .select() ==
            SDS_Company.DataSourceMode = SqlDataSourceMode.DataSet;

            dv_ddl = (DataView)SDS_Company.Select(args);

            return dv_ddl;

        }
        catch (Exception ex)
        {
            //Response.Write(e.Message);
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DDL_DEPT_DBINIT", ex.Message, "", "c#", "0", ex.StackTrace);
            return null;

        }
    }
    protected DataView DDL_ROLE_DBINIT()
    {
        try
        {
            string v_SQL;
            DataView dv_ddl = new DataView();
            DataSourceSelectArguments args = new DataSourceSelectArguments();
            //== 2.撰寫SQL指令 ==
            v_SQL = "SELECT ROLE_ID,ROLE_ID || '-' || ROLE_NAME as ROLE_NAME FROM MIS_ROLE_AP order by ROLE_ID ";

            SDS_Company.SelectParameters.Clear();
            SDS_Company.SelectCommand = v_SQL;

            //== 3.執行SQL指令 .select() ==
            SDS_Company.DataSourceMode = SqlDataSourceMode.DataSet;

            dv_ddl = (DataView)SDS_Company.Select(args);

            return dv_ddl;

        }
        catch (Exception ex)
        {
            //Response.Write(e.Message);
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DDL_ROLE_DBINIT", ex.Message, "", "c#", "0", ex.StackTrace);
            return null;

        }
    }
    protected void DV1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        try
        {

            DV1.EditIndex = e.NewEditIndex;
            DBINIT();
        }
        catch (Exception ex)
        {
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_RowEditing", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }
    protected void DV1_ItemCanceling(object sender, ListViewCancelEventArgs e)
    {
        try
        {
            lbl_Message.Text = "";
            DV1.EditIndex = -1;
            DBINIT();
        }
        catch (Exception ex)
        {
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_ItemCanceling", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }
    protected void DV1_ItemEditing(object sender, ListViewEditEventArgs e)
    {
        try
        {

            DV1.EditIndex = e.NewEditIndex;
            DBINIT();
        }
        catch (Exception ex)
        {
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_ItemEditing", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }
    protected void DV1_ItemUpdating(object sender, ListViewUpdateEventArgs e)
    {
        int index;
        string v_USER_ID, v_USER_NAME, v_USER_PASSWORD, v_ROLE_ID, v_DEPT_ID, v_PARENT_USER_ID, v_EMAIL_1, v_EMAIL_2;
        TextBox TB = new TextBox();
        try
        {
            index = e.ItemIndex;
            v_USER_ID = (string)DV1.DataKeys[index].Value;
            // EmptyDataTemplate
            v_USER_NAME = ((TextBox)DV1.EditItem.FindControl("txtUSER_NAME_1")).Text.Trim();
            v_USER_PASSWORD = ((TextBox)DV1.EditItem.FindControl("txtUSER_PASSWORD")).Text.Trim();
            DropDownList ddlDEPT_ID = (DropDownList)DV1.EditItem.FindControl("ddlDEPT_ID_1");
            v_DEPT_ID = ddlDEPT_ID.SelectedValue;
            DropDownList ddlROLE_ID = (DropDownList)DV1.EditItem.FindControl("ddlROLE_ID_1");
            v_ROLE_ID = ddlROLE_ID.SelectedValue;
            v_PARENT_USER_ID = ((TextBox)DV1.EditItem.FindControl("txtPARENT_USER_ID")).Text.Trim();
            v_EMAIL_1 = ((TextBox)DV1.EditItem.FindControl("txtE_MAIL1")).Text.Trim();
            v_EMAIL_2 = ((TextBox)DV1.EditItem.FindControl("txtE_MAIL2")).Text.Trim();
            TB = ((TextBox)DV1.EditItem.FindControl("txtPARENT_USER_ID"));
            lbl_Message.Text = string.Empty;
            if (check(v_PARENT_USER_ID) == false)
            {
                lbl_Message.Text = "上層使用者代號必需存在於使用者代號檔中!";
                TB.Focus();
                e.Cancel = true;
            }
            else
            {

                //'==下面的程式，只是作SQL指令的更新動作而已
                //'== 設定SQL指令將會用到的參數 ==
                SDS_Company.SelectParameters.Clear();
                SDS_Company.UpdateParameters.Add("USER_NAME", v_USER_NAME);
                SDS_Company.UpdateParameters.Add("USER_PASSWORD", v_USER_PASSWORD);
                SDS_Company.UpdateParameters.Add("DEPT_ID", v_DEPT_ID);
                SDS_Company.UpdateParameters.Add("ROLE_ID", v_ROLE_ID);
                SDS_Company.UpdateParameters.Add("PARENT_USER_ID", v_PARENT_USER_ID);
                SDS_Company.UpdateParameters.Add("E_MAIL1", v_EMAIL_1);
                SDS_Company.UpdateParameters.Add("E_MAIL2", v_EMAIL_2);
                SDS_Company.UpdateParameters.Add("UPD_ID", User.Identity.Name);
                SDS_Company.UpdateParameters.Add("UPD_DATE", System.DateTime.Now.ToString("yyyyMMdd"));
                SDS_Company.UpdateParameters.Add("UPD_TIME", System.DateTime.Now.ToString("HHmmss"));
                SDS_Company.UpdateParameters.Add("USER_ID", v_USER_ID);

                SDS_Company.UpdateCommand = "UPDATE MIS_USER_AP SET USER_NAME = :USER_NAME, USER_PASSWORD = :USER_PASSWORD, DEPT_ID = :DEPT_ID, ROLE_ID = :ROLE_ID, PARENT_USER_ID = :PARENT_USER_ID, E_MAIL1 = :E_MAIL1, E_MAIL2 = :E_MAIL2, UPD_ID = :UPD_ID, UPD_DATE = :UPD_DATE, UPD_TIME = :UPD_TIME WHERE USER_ID = :USER_ID";
                SDS_Company.Update();
                //'==========================================
                //'==== 離開編輯模式 ====
                DV1.EditIndex = -1;
                DBINIT();
            }




        }
        catch (Exception ex)
        {
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_ItemUpdating", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }
    protected void DV1_ItemDeleting(object sender, ListViewDeleteEventArgs e)
    {
        int index;
        string v_USER_ID;
        try
        {
            index = e.ItemIndex;
            v_USER_ID = (string)DV1.DataKeys[index].Value;

            //'==下面的程式，只是作SQL指令的更新動作而已
            //'== 設定SQL指令將會用到的參數 ==
            SDS_Company.DeleteParameters.Add("USER_ID", v_USER_ID);
            SDS_Company.DeleteCommand = "Delete MIS_USER_AP Where USER_ID = :USER_ID";
            SDS_Company.Delete();
            DBINIT();

        }
        catch (Exception ex)
        {
            lbl_Message.Text = ex.Message;
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_ItemDeleting", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }
    protected void btnQuery_Click(object sender, EventArgs e)
    {
        var controlItem = new List<ControlItem>() 
        {
          new ControlItem("txtUSER_ID", txtUSER_ID.Text, txtUSER_ID.GetType()),
          new ControlItem("txtUSER_NAME", txtUSER_NAME.Text, txtUSER_NAME.GetType()),  
          new ControlItem("ddlDEPT_ID", ddlDEPT_ID.SelectedValue, ddlDEPT_ID.GetType()) ,
          new ControlItem("ddlROLE_ID", ddlROLE_ID.SelectedValue, ddlROLE_ID.GetType()) 
        };
        ConditionRecordUtil.RecordSearchConditionData(controlItem);


        DBINIT();
        lbl_Message.Text = string.Empty;
        int PageSize = ((DataPager)DV1.FindControl("DataPager1")).PageSize;
        ((DataPager)DV1.FindControl("DataPager1")).SetPageProperties(0, PageSize, true);
        //Adams:設定頁數相關功能
        SetPagerInfo(PageSize);
        
    }
    protected void DV1_ItemInserting(object sender, ListViewInsertEventArgs e)
    {
        string v_USER_ID, v_USER_NAME, v_PARENT_USER_ID, v_USER_PASSWORD, v_ROLE_ID, v_DEPT_ID, v_E_MAIL1, v_E_MAIL2;
        TextBox TB = new TextBox();
        try
        {
            v_USER_ID = ((TextBox)DV1.InsertItem.FindControl("txtUSER_ID_1")).Text.Trim();
            // EmptyDataTemplate
            v_USER_NAME = ((TextBox)DV1.InsertItem.FindControl("txtUSER_NAME_1")).Text.Trim();
            v_PARENT_USER_ID = ((TextBox)DV1.InsertItem.FindControl("txtPARENT_USER_ID")).Text.Trim();
            v_USER_PASSWORD = ((TextBox)DV1.InsertItem.FindControl("txtUSER_PASSWORD")).Text.Trim();
            DropDownList ddlDEPT_ID = (DropDownList)DV1.InsertItem.FindControl("ddlDEPT_ID_1");
            v_DEPT_ID = ddlDEPT_ID.SelectedValue;
            DropDownList ddlROLE_ID = (DropDownList)DV1.InsertItem.FindControl("ddlROLE_ID_1");
            v_ROLE_ID = ddlROLE_ID.SelectedValue;
            v_E_MAIL1 = ((TextBox)DV1.InsertItem.FindControl("txtE_MAIL1")).Text.Trim();
            v_E_MAIL2 = ((TextBox)DV1.InsertItem.FindControl("txtE_MAIL2")).Text.Trim();
            TB = ((TextBox)DV1.InsertItem.FindControl("txtPARENT_USER_ID"));
            lbl_Message.Text = string.Empty;
            if (check(v_PARENT_USER_ID) == false)
            {
                lbl_Message.Text = "上層使用者代號必需存在於使用者代號檔中!";
                TB.Focus();
                e.Cancel = true;
            }
            else
            {

                //'==下面的程式，只是作SQL指令的更新動作而已
                //'== 設定SQL指令將會用到的參數 ==
                SDS_Company.InsertParameters.Clear();

                SDS_Company.InsertParameters.Add("USER_ID", v_USER_ID);
                SDS_Company.InsertParameters.Add("USER_NAME", v_USER_NAME);
                SDS_Company.InsertParameters.Add("USER_PASSWORD", v_USER_PASSWORD);
                SDS_Company.InsertParameters.Add("DEPT_ID", v_DEPT_ID);
                SDS_Company.InsertParameters.Add("ROLE_ID", v_ROLE_ID);
                SDS_Company.InsertParameters.Add("PARENT_USER_ID", v_PARENT_USER_ID);
                SDS_Company.InsertParameters.Add("E_MAIL1", v_E_MAIL1);
                SDS_Company.InsertParameters.Add("E_MAIL2", v_E_MAIL2);
                SDS_Company.InsertParameters.Add("CRT_ID", User.Identity.Name);
                SDS_Company.InsertParameters.Add("CRT_DATE", System.DateTime.Now.ToString("yyyyMMdd"));
                SDS_Company.InsertParameters.Add("CRT_TIME", System.DateTime.Now.ToString("HHmmss"));
                SDS_Company.InsertParameters.Add("UPD_ID", User.Identity.Name);
                SDS_Company.InsertParameters.Add("UPD_DATE", System.DateTime.Now.ToString("yyyyMMdd"));
                SDS_Company.InsertParameters.Add("UPD_TIME", System.DateTime.Now.ToString("HHmmss"));


                SDS_Company.InsertCommand = "insert into MIS_USER_AP (USER_ID, USER_NAME, USER_PASSWORD, DEPT_ID, ROLE_ID, parent_USER_ID, E_MAIL1, E_MAIL2, crt_id, crt_date, crt_time, upd_id, upd_date, upd_time) values(:USER_ID, :USER_NAME, :USER_PASSWORD, :DEPT_ID, :ROLE_ID, :parent_USER_ID, :E_MAIL1, :E_MAIL2, :CRT_ID, :crt_date, :crt_time, :upd_id, :upd_date, :upd_time)";
                SDS_Company.Insert();
                //'==========================================
                DBINIT();
            }

        }

        catch (Exception ex)
        {
            //Response.Write(ex.Message);
            //PK重覆
            if (ex.Message.IndexOf("ORA-00001") >= 0)
            {
                lbl_Message.Text = "輸入的使用者代號已存在使用者檔中！";
            }
            else
            {
                lbl_Message.Text = ex.Message;
            }
            ERROR_DBSAVE("bas_user.aspx.cs", "DV1_ItemInserting", ex.Message, "", "c#", "0", ex.StackTrace);
        }
    }

    protected void DV1_PagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
    {
        DV1.SelectedIndex = -1;        
        //Adams:設定挑選的頁數變化
        DataPager dp = ((DataPager)DV1.FindControl("DataPager1")) as DataPager;
        SetGoToPageDropDownList(e.StartRowIndex, e.MaximumRows, dp.TotalRowCount);
    }
    protected void DV1_PagePropertiesChanged(object sender, EventArgs e)
    {
        int PageSize = ((DataPager)DV1.FindControl("DataPager1")).PageSize;
        int StartRowIndex = ((DataPager)DV1.FindControl("DataPager1")).StartRowIndex;
        int TotalRowCount = ((DataPager)DV1.FindControl("DataPager1")).TotalRowCount;
        ((DataPager)DV1.FindControl("DataPager1")).SetPageProperties(StartRowIndex, PageSize, true);
        DBINIT();
    }

    protected Boolean check(string p_PARENT_USER_ID)
    {
        //判斷PARENT_USER_ID必需存在USER_ID
        if (p_PARENT_USER_ID != string.Empty)
        {
            DataSourceSelectArguments args = new DataSourceSelectArguments();
            DataView DV = new DataView();

            SDS_Company.SelectCommand = "SELECT USER_ID FROM MIS_USER_AP Where USER_ID = :USER_ID ";
            SDS_Company.SelectParameters.Clear();
            SDS_Company.SelectParameters.Add("USER_ID", TypeCode.String, p_PARENT_USER_ID);

            //== 3.執行SQL指令 .select() ==
            SDS_Company.DataSourceMode = SqlDataSourceMode.DataSet;
            DV = (DataView)SDS_Company.Select(args);
            if (DV.Table.Rows.Count == 0)
            {
                return false;
            }

        }
        return true;
    }

    protected void DV1_ItemCreated(object sender, ListViewItemEventArgs e)
    {
        if (e.Item.ItemType == ListViewItemType.InsertItem)
        {
            DropDownList ddl = (DropDownList)e.Item.FindControl("ddlDEPT_ID_1");

            if (ddl != null)
            {
                ddl.DataSource = dv_ddl_1;
                //ddl.DataBind();

            }

            ddl = (DropDownList)e.Item.FindControl("ddlROLE_ID_1");

            if (ddl != null)
            {
                ddl.DataSource = dv_ddl_2;
                //ddl.DataBind();

            }
        }
    }

    protected void DV1_ItemDataBound(object sender, ListViewItemEventArgs e)
    {
        if (DV1.EditIndex == (e.Item as ListViewDataItem).DataItemIndex)
        {
            DropDownList ddl = (DropDownList)e.Item.FindControl("ddlDEPT_ID_1");

            if (ddl != null)
            {
                ddl.DataSource = dv_ddl_1;
                ddl.DataBind();
                string selectedValue = (e.Item.FindControl("txtDEPT_ID_1") as TextBox).Text;
                ddl.SelectedValue = selectedValue;

            }

            ddl = (DropDownList)e.Item.FindControl("ddlROLE_ID_1");

            if (ddl != null)
            {
                ddl.DataSource = dv_ddl_2;
                ddl.DataBind();
                string selectedValue = (e.Item.FindControl("txtROLE_ID_1") as TextBox).Text;
                ddl.SelectedValue = selectedValue;

            }
        }
    }
    protected void btn_exist_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/Default.aspx");
    }

    //Adams:設定頁數相關功能
    protected void uxOnePageCountDropDownList_SelectedIndexChanged(object sender, EventArgs e)
    {
      SetPagerInfo(int.Parse(((DropDownList)sender).SelectedValue));
    }
    //Adams:設定頁數相關功能
    protected void uxGoToPageDropDownList_SelectedIndexChanged(object sender, EventArgs e)
    {
      DropDownList ddl = sender as DropDownList;
      DataPager dp = (DataPager)DV1.FindControl("DataPager1") as DataPager;
      int StartRowIndex = int.Parse(((DropDownList)sender).SelectedValue) * dp.PageSize;
      dp.SetPageProperties(StartRowIndex, dp.PageSize, true);
    }

    protected void SetPagerInfo(int PageSize)
    {
      //Adams:顯示分頁處的總頁數
      DataPager dp = (DataPager)DV1.FindControl("DataPager1") as DataPager;
      dp.PageSize = PageSize;
      int PageCount = (dp.TotalRowCount / dp.PageSize) + 1;
      Label lbl_total_page_count = (Label)DV1.FindControl("lbl_total_page_count") as Label;
      lbl_total_page_count.Text = PageCount.ToString();
      Label lbl_total_counter = (Label)DV1.FindControl("lbl_total_counter") as Label;
      lbl_total_counter.Text = dp.TotalRowCount.ToString();
      //跳到指定頁數   
      SetGoToPageDropDownList(dp.StartRowIndex, dp.PageSize, dp.TotalRowCount);
    }

    //Adams:設定頁數相關功能
    protected void SetGoToPageDropDownList(int StartRowIndex, int PageSize, int TotalRowCount)
    {
      int PageIndex = StartRowIndex / PageSize;
      int PageCount = TotalRowCount / PageSize;
      DropDownList GoToPageDropDownList = DV1.FindControl("uxGoToPageDropDownList") as DropDownList;
      GoToPageDropDownList.Items.Clear();
      for (int i = 0; i <= PageCount; i++)
      {
        GoToPageDropDownList.Items.Add(new ListItem((i + 1).ToString(), i.ToString()));
      }
      GoToPageDropDownList.SelectedValue = (PageIndex).ToString();
    }
}